﻿Imports System.Data.SqlClient

Public Class PaqueteDAO


    Public Function listar() As Collection
        Dim lista As New Collection

        Try
            Dim cn As New SqlConnection(ConexionBD.obtenerCadenaConexion())
            cn.Open()

            Dim sql = "SELECT IDPAQUETE, DESCRIPCION, PAGO, PRODUCTOS FROM PAQUETE"
            Dim cmd As New SqlCommand(sql, cn)

            Dim dr As SqlDataReader
            dr = cmd.ExecuteReader()
            While dr.Read
                Dim omen As New Paquete
                omen.idpaquete = dr(0).ToString
                omen.descripcion = dr(1).ToString
                omen.pago = dr(2).ToString
                omen.productos = dr(3).ToString
                lista.Add(omen)
            End While

            dr.Close()
            cn.Close()

        Catch ex As SqlException
            Throw New DAOExcepcion(ex.ToString)
        End Try

        Return lista
    End Function

    Public Function insertar(ByVal omen As Paquete) As Paquete
        Try
            Dim cn As New SqlConnection(ConexionBD.obtenerCadenaConexion())
            cn.Open()

            Dim sql = "INSERT INTO PAQUETE (DESCRIPCION, PAGO, PRODUCTOS) VALUES (@VALOR1, @VALOR2, @VALOR3)"

            Dim cmd As New SqlCommand(sql, cn)
            cmd.Parameters.AddWithValue("@VALOR1", omen.descripcion)
            cmd.Parameters.AddWithValue("@VALOR2", omen.pago)
            cmd.Parameters.AddWithValue("@VALOR3", omen.productos)

            Dim afectados = cmd.ExecuteNonQuery

            If afectados <> 1 Then
                Throw New DAOExcepcion("PaqueteDAO: No insertó")
            End If

            ' LEER EL ULTIMO ID
            sql = "SELECT @@IDENTITY"
            cmd = New SqlCommand(sql, cn)
            Dim dr As SqlDataReader
            dr = cmd.ExecuteReader()
            If dr.Read Then
                omen.idpaquete = CType(dr(0), Integer)
            End If

            dr.Close()
            cn.Close()

        Catch ex As Exception
            Throw New DAOExcepcion(ex.ToString)
        End Try

        Return omen

    End Function



    Public Sub actualizar(ByVal omen As Paquete)
        Try
            Dim cn As New SqlConnection(ConexionBD.obtenerCadenaConexion())
            cn.Open()

            Dim sql = "UPDATE PAQUETE SET DESCRIPCION=@VALOR1, PAGO=@VALOR2, PRODUCTOS=@VALOR3 WHERE IDPAQUETE=@VALOR4"

            Dim cmd As New SqlCommand(sql, cn)
            cmd.Parameters.AddWithValue("@VALOR1", omen.descripcion)
            cmd.Parameters.AddWithValue("@VALOR2", omen.pago)
            cmd.Parameters.AddWithValue("@VALOR3", omen.productos)
            cmd.Parameters.AddWithValue("@VALOR4", omen.idPaquete)

            Dim afectados = cmd.ExecuteNonQuery

            If afectados <> 1 Then
                Throw New DAOExcepcion("PaqueteDAO: No actualizó")
            End If

            cn.Close()

        Catch ex As Exception
            MsgBox(ex.Message)
            Throw New DAOExcepcion(ex.ToString)
        End Try
    End Sub



    Public Sub eliminar(ByVal idPaquete As Integer)
        Try
            Dim cn As New SqlConnection(ConexionBD.obtenerCadenaConexion())
            cn.Open()

            Dim sql = "DELETE FROM PAQUETE WHERE idPaquete=@VALOR1"

            Dim cmd As New SqlCommand(sql, cn)
            cmd.Parameters.AddWithValue("@VALOR1", idPaquete)

            Dim afectados = cmd.ExecuteNonQuery

            If afectados <> 1 Then
                Throw New DAOExcepcion("PaqueteDAO: No eliminó")
            End If

            cn.Close()

        Catch ex As Exception
            Throw New DAOExcepcion(ex.ToString)

        End Try
    End Sub



    
End Class
